﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DAL
{
    public class BeatMakers 
    {
        public bool GetBMs(out List<Util.BeatMaker> bmList)
        {
            SqlDataReader sdr;
            bmList = new List<Util.BeatMaker>();

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;
                con.Open();
                //выборка пользователетей с ролью битмейкер
                String strSQL = "SELECT aspnet_Users.UserName FROM aspnet_Users INNER JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId INNER JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId WHERE (RoleName = 'BeatMaker')";
                SqlCommand comm = new SqlCommand(strSQL, con);
                sdr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        bmList.Add(new Util.BeatMaker() { Login = sdr["UserName"].ToString() });
                    }
                }
            }
            return true;
        }

        public bool GetBM(string login, out Util.BeatMaker bm)
        {
            SqlDataReader sdr;
            bm = new Util.BeatMaker();

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;
                con.Open();
                //выборка пользователетя с ролью битмейкер и заданным логином
                String strSQL = "SELECT aspnet_Users.UserName FROM aspnet_Users INNER JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId INNER JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId WHERE (RoleName = 'BeatMaker' AND UserName=@login)";
                SqlCommand comm = new SqlCommand(strSQL, con);
                
                SqlParameter param = new SqlParameter();
                param.ParameterName = "@login";
                param.Value = login;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                sdr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        bm = new Util.BeatMaker() { Login = sdr["UserName"].ToString() };
                    }
                }
            }
            return true;
        }

        public bool GetAdms(out List<Util.BeatMaker> bmList)
        {
            SqlDataReader sdr;
            bmList = new List<Util.BeatMaker>();

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;
                con.Open();
                //выборка пользователетей с ролью битмейкер
                String strSQL = "SELECT aspnet_Users.UserName FROM aspnet_Users INNER JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId INNER JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId WHERE (RoleName = 'Administrator')";
                SqlCommand comm = new SqlCommand(strSQL, con);
                sdr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        bmList.Add(new Util.BeatMaker() { Login = sdr["UserName"].ToString() });
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// функция проверяет, существует ли битмейкер
        /// </summary>
        /// <param name="bmLogin">имя битмейкера, проверку существования которого нужно осуществить</param>
        /// <returns>истина - бимейкер существует, ложь - не существует битмейкера с таким именем</returns>
        public bool ValidBM(string bmLogin)
        {
            SqlDataReader sdr;
            int count = 0;
            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;
                con.Open();
                //выборка пользователетя с ролью битмейкер и заданным логином
                String strSQL = "SELECT count(aspnet_Users.UserName) as Count FROM aspnet_Users INNER JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId INNER JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId WHERE (RoleName = 'BeatMaker' AND UserName=@login)";
                SqlCommand comm = new SqlCommand(strSQL, con);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@login";
                param.Value = bmLogin;
                param.SqlDbType = SqlDbType.NVarChar;
                comm.Parameters.Add(param);

                sdr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        count = sdr.GetInt32(0);
                    }
                }
            }

            if (count > 0)
                return true;
            else
                return false;
        }


        public bool GetUsers(out List<Util.User> usList)
        {
            SqlDataReader sdr;
            usList = new List<Util.User>();

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["FProj"].ConnectionString;
                con.Open();
                //выборка пользователетей с ролью битмейкер
                String strSQL = "SELECT aspnet_Users.UserName FROM aspnet_Users INNER JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId INNER JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId WHERE (RoleName = 'User')";
                SqlCommand comm = new SqlCommand(strSQL, con);
                sdr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        usList.Add(new Util.BeatMaker() { Login = sdr["UserName"].ToString() });
                    }
                }
            }
            return true;
        }
    }
}